package com.dao.library;

import java.util.ArrayList;
import java.util.List;

import com.bean.DBClasses.DBExamplePaperInfo;
import com.bean.DBClasses.DBPapers;
import com.bean.DBClasses.DBStudentPaper;
import com.bean.DBClasses.DBStudents;
import com.bean.library.DbPaper;
import com.bean.library.PaperInfo;
import com.bean.library.StudentExamInfo;
import com.dao.BaseDao;
import com.dao.PostgreSQL;

public class PaperInfoDaoImpl extends BaseDao implements PaperInfoDao,PostgreSQL{

	@Override
	public PaperInfo getPaperInfoByPaperId(int paperId) throws Exception {
		// TODO Auto-generated method stub
		/*PaperInfo是bean中的PaperInfo
		字段如下:
		1、int paperId;试卷id	setPaperId()
		2、int averageScore;平均分 setAverageScore()
		3、int submitNum;提交人数 setSubmitNum()
		4、int passNum;通过人数 setPassNum() 
		5、ArrayList<StudentExamInfo> studentExamInfoList;学生考试信息列表  setStudentExamInfoList
			StudentExamInfo中有3个字段
				1、String studentId;学生id  setStudentId()
				2、String studentName;学生姓名   setStudentName()
				3、int studentScore;这个学生的这张试卷的考试得分     setStudentScore()
		*/
		PaperInfo paperInfo = new PaperInfo();
		String  examPaperInfoSql = "select average_score,student_count  from examPaperInfo where paper_id = ?";
		DBExamplePaperInfo dataBaseExamplePaperInfo = esql.query(DBExamplePaperInfo.class, examPaperInfoSql, paperId);
		paperInfo.setAverageScore(dataBaseExamplePaperInfo.getAverageScore());
		paperInfo.setSubmitNum(dataBaseExamplePaperInfo.getStudentCount());
		paperInfo.setPaperId(paperId);
		String paperSql = "select pass_score from papers where paper_id =?";
		DBPapers dataBasePaper = esql.query(DBPapers.class, paperSql, paperId);
		int passScore = dataBasePaper.getPassScore();
		String studentPaperSql = "select count(student_id) as paper_id from student_paper where paper_id =? and score>?";
		DBStudentPaper dataBaseSutdentPaper = esql.query(DBStudentPaper.class, studentPaperSql, paperId,passScore);
		paperInfo.setPassNum(dataBaseSutdentPaper.getPaperId());
		studentPaperSql = "select student_id,score from studnet_paper where paper_id = ?";
		List<DBStudentPaper> dataBaseStudentPaperSet = esql.list(DBStudentPaper.class, studentPaperSql, paperId);
		int countOfStudent = dataBaseStudentPaperSet.size();
		ArrayList<StudentExamInfo> studentExamInfoList =new ArrayList<StudentExamInfo>(); 
		for(int i=0 ;i<countOfStudent;i++){
			String studentId = dataBaseStudentPaperSet.get(i).getStudentId();
			StudentExamInfo studentExamInfo = new StudentExamInfo();
			String studentSql ="select student_name from students where student_id = ?";
			DBStudents dataBaseStudent = esql.query(DBStudents.class, studentSql, studentId);
			studentExamInfo.setStudentId(studentId);
			studentExamInfo.setStudentName(dataBaseStudent.getStudentName());
			studentExamInfo.setStudentScore(dataBaseStudentPaperSet.get(i).getScore());
		}
		paperInfo.setStudentExamInfoList(studentExamInfoList);
		return paperInfo;
	}
	
}
